package com.jddup.program.dtm.sqlserver;

import com.jddup.common.pojo.TableColumn;
import com.jddup.common.pojo.TableMeta;
import com.jddup.context.JddConfigContext;
import com.jddup.program.dtm.DbToTableMetaConvertor;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by ZZB on 2017/12/4.
 */
public class SqlServerDbToTableMetaConvertor implements DbToTableMetaConvertor {
    private static final String TableMetaSQL =
                    "SELECT\n" +
                    "    sys.columns.name as columnName,\n" +
                    "    case when sys.types.name like '%char%' THEN\n" +
                    "         '['+sys.types.name+']('+\n" +
                    "         (\n" +
                    "         case when sys.columns.max_length != -1 then\n" +
                    "            cast(sys.columns.max_length as VARCHAR(10))\n" +
                    "         else '8000' end )\n" +
                    "         + ')'\n" +
                    "         when sys.types.name like '%num%' or sys.types.name like '%decimal%' THEN\n" +
                    "         '['+sys.types.name+']('+CAST(sys.columns.precision AS VARCHAR(10))+','+CAST(sys.columns.scale AS VARCHAR(10))+')'\n" +
                    "    else '['+sys.types.name+']' end\n" +
                    "    as typeFullName,\n" +
                    "    sys.types.name as columnType,\n" +
                    "    sys.columns.max_length as maxLength,\n" +
                    "    sys.columns.precision,\n" +
                    "    sys.columns.scale,\n" +
                    "    sys.columns.is_nullable as isNullable,\n" +
                    "    sys.extended_properties.value as comment\n" +
                    "FROM\n" +
                    "    sys.columns\n" +
                    "inner join  sys.tables on sys.columns.object_id = sys.tables.object_id\n" +
                    "inner join  sys.types on sys.columns.user_type_id = sys.types.user_type_id\n" +
                    "inner join  sys.schemas on sys.schemas.schema_id = sys.tables.schema_id\n" +
                    "left join sys.extended_properties  ON sys.extended_properties.major_id =sys.columns.object_id AND sys.extended_properties.minor_id = sys.columns.column_id\n" +
                    "WHERE 1=1 and sys.tables.name = '{tableName}'\n" +
                    "order by sys.columns.column_id asc\n" +
                    " ";

    @Override
    public TableMeta convertFromTableName(String tableName, String datasourceAlias) throws SQLException {
        TableMeta tableMeta = new TableMeta();
        tableMeta.setTableName(tableName);
        List<TableColumn> list = new ArrayList<>();

        Connection connection = JddConfigContext.getDataSourceMap().get(datasourceAlias).getConnection();

        if (tableExist(connection, tableName)) {
            String schema = connection.getCatalog();
            String sql = TableMetaSQL.replace("{tableName}", tableName).replace("{dbName}", schema);
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                TableColumn tableColumn = new TableColumn();
                tableColumn.setColumnName(rs.getString("columnName"));
                tableColumn.setColumnType(rs.getString("columnType"));
                tableColumn.setMaxLength(rs.getLong("maxLength"));
                tableColumn.setPrecision(rs.getLong("precision"));
                tableColumn.setScale(rs.getLong("scale"));
                tableColumn.setNullable(rs.getBoolean("isNullable"));
                tableColumn.setPrimaryKey(false);
                tableColumn.setSigned(false);
                tableColumn.setDefaultValue("");
                tableColumn.setTypeFullName(rs.getString("typeFullName"));
                list.add(tableColumn);
                if (tableColumn.getPrimaryKey()) {
                    tableMeta.setPrimaryKeyColumn(tableColumn);
                }
            }
        } else {
            tableMeta.setTableExist(false);
        }

        connection.close();
        tableMeta.setTableColumns(list);
        return tableMeta;
    }

    @Override
    public boolean tableExist(Connection connection, String tableName) {
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select 1 from [" + tableName + "] limit 1");
            resultSet.next();
            resultSet.close();
            statement.close();
            return true;
        } catch (SQLException e) {
            return false;
        }
    }
}
